package com.ljz.dao.daoImpl;

import com.ljz.dao.DeptDao;
import com.ljz.entity.Dept;
import com.ljz.untils.DBUtils2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeptDaoImpl implements DeptDao {
    private Connection conn = DBUtils2.getConn();
    @Override
    public List<Dept> findAll() {
        String sql = "select * from dept";
        Connection conn = DBUtils2.getConn();
        PreparedStatement ps = null;
        ResultSet resultSet =null;
        List<Dept> list = new ArrayList<>();
        try {
            if (conn != null) {
                ps = conn.prepareStatement(sql);
            }
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Dept dept = new Dept();
                dept.setDeptno(resultSet.getInt("deptno"));
                dept.setDname(resultSet.getString("dname"));
                dept.setLoc(resultSet.getString("loc"));
                list.add(dept);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils2.closeAll(resultSet, ps, conn);
        }
        return list;
    }

    @Override
    public Dept findById(int deptno) {
        String sql = "select * from dept where deptno = ?";
        Connection conn = DBUtils2.getConn();
        PreparedStatement ps = null;
        ResultSet resultSet =null;
        Dept dept = new Dept();
        try {
            if (conn != null) {
                ps = conn.prepareStatement(sql);
            }
            ps.setInt(1, deptno);
            resultSet = ps.executeQuery();
            dept.setDname(resultSet.getString("dname"));
            dept.setLoc(resultSet.getString("loc"));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils2.closeAll(resultSet, ps, conn);
        }
        return dept;
    }

    @Override
    public int add(Dept dept) {
        String sql = "insert into dept(dname,loc,deptno) values(?,?,?)";
        List<Object> list = new ArrayList<>();
        list.add(dept.getDname());
        list.add(dept.getLoc());
        list.add(dept.getDeptno());
        int num = DBUtils2.exceUpdate(sql, list);
        return num;
    }

    @Override
    public int update(Dept dept) {
        String sql = "update dept set dname = ?,loc = ? where deptno = ?";
        List<Object> list = new ArrayList<>();
        list.add(dept.getDname());
        list.add(dept.getLoc());
        list.add(dept.getDeptno());
        int num = DBUtils2.exceUpdate(sql, list);
        return num;
    }

    @Override
    public int delete(int deptno) {
        String sql = "delete from dept where deptno = ?";
        List<Object> list = new ArrayList<>();
        list.add(deptno);
        int num = DBUtils2.exceUpdate(sql, list);
        return num;
    }
}
